package com.pdd.mall.controller;

import com.pdd.mall.common.api.CommonResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.util.stream.Collectors;

/**
 * 数据库初始化控制器
 */
@RestController
@RequestMapping("/api/db")
public class DbInitController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 添加tracking_no字段
     */
    @GetMapping("/add-tracking-no")
    public CommonResult<String> addTrackingNoField() {
        try (InputStream inputStream = getClass().getResourceAsStream("/db/add_tracking_no.sql")) {
            // 读取SQL文件
            if (inputStream == null) {
                return CommonResult.failed("SQL文件不存在");
            }

            String sql;
            try (BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8))) {
                sql = reader.lines().collect(Collectors.joining("\n"));
            }

            try {
                // 执行SQL
                jdbcTemplate.execute(sql);
                return CommonResult.success("添加tracking_no字段成功");
            } catch (DataAccessException e) {
                // 检查是否是"列已存在"的错误
                String errorMessage = e.getMessage().toLowerCase();
                if (errorMessage.contains("duplicate") || errorMessage.contains("already exists")) {
                    return CommonResult.success("tracking_no字段已存在，无需添加");
                } else {
                    throw e; // 重新抛出其他类型的错误
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return CommonResult.failed("添加tracking_no字段失败: " + e.getMessage());
        }
    }

    /**
     * 直接执行SQL添加tracking_no字段
     */
    @GetMapping("/add-tracking-no-direct")
    public CommonResult<String> addTrackingNoFieldDirect() {
        try {
            // 直接执行SQL语句
            try {
                jdbcTemplate.execute("ALTER TABLE `order` ADD COLUMN `tracking_no` varchar(50) DEFAULT NULL COMMENT '物流单号' AFTER `delivery_time`");
                return CommonResult.success("添加tracking_no字段成功");
            } catch (DataAccessException e) {
                // 检查是否是"列已存在"的错误
                String errorMessage = e.getMessage().toLowerCase();
                if (errorMessage.contains("duplicate") || errorMessage.contains("already exists")) {
                    return CommonResult.success("tracking_no字段已存在，无需添加");
                } else {
                    throw e; // 重新抛出其他类型的错误
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return CommonResult.failed("添加tracking_no字段失败: " + e.getMessage());
        }
    }

    /**
     * 创建评价表
     */
    @GetMapping("/create-review-table")
    public CommonResult<String> createReviewTable() {
        try {
            // 读取SQL文件
            try (InputStream inputStream = getClass().getResourceAsStream("/db/review_table.sql")) {
                if (inputStream == null) {
                    return CommonResult.failed("SQL文件不存在");
                }

                String sql;
                try (BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8))) {
                    sql = reader.lines().collect(Collectors.joining("\n"));
                }

                // 执行SQL
                jdbcTemplate.execute(sql);
                return CommonResult.success("创建评价表成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return CommonResult.failed("创建评价表失败: " + e.getMessage());
        }
    }

    /**
     * 添加商品原价字段
     */
    @GetMapping("/add-original-price")
    public CommonResult<String> addOriginalPriceField() {
        try {
            // 读取SQL文件
            try (InputStream inputStream = getClass().getResourceAsStream("/db/add_original_price.sql")) {
                if (inputStream == null) {
                    return CommonResult.failed("SQL文件不存在");
                }

                String sql;
                try (BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream, StandardCharsets.UTF_8))) {
                    sql = reader.lines().collect(Collectors.joining("\n"));
                }

                try {
                    // 执行SQL
                    String[] statements = sql.split(";");
                    for (String statement : statements) {
                        if (!statement.trim().isEmpty()) {
                            jdbcTemplate.execute(statement);
                        }
                    }
                    return CommonResult.success("添加商品原价字段成功");
                } catch (DataAccessException e) {
                    // 检查是否是"列已存在"的错误
                    String errorMessage = e.getMessage().toLowerCase();
                    if (errorMessage.contains("duplicate") || errorMessage.contains("already exists")) {
                        return CommonResult.success("商品原价字段已存在，无需添加");
                    } else {
                        throw e; // 重新抛出其他类型的错误
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            return CommonResult.failed("添加商品原价字段失败: " + e.getMessage());
        }
    }
}
